using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Configuration;

////-------------------------------------------------------------------------------------------------------------- 
//// Public Enumerator 
//// Purpose: This defines all of the data types that can be used by the SqlService 
////-------------------------------------------------------------------------------------------------------------- 
public enum ssenumSqlDataTypes
{
    ssSDT_Bit,
    ssSDT_DateTime,
    ssSDT_Decimal,
    ssSDT_Integer,
    ssSDT_Money,
    ssSDT_String
}

////-------------------------------------------------------------------------------------------------------------- 
//// Public Class 
//// Purpose: The SqlService class interacts with a datasource using ADO.Net's 
//// SqlClient interface. 
////-------------------------------------------------------------------------------------------------------------- 
public class SqlService
{

    ////---------------------------------------------------------------------------------------------------- 
    //// Declare Class level variables 
    ////---------------------------------------------------------------------------------------------------- 
    ////--- The Database login User ID 
    private string m_sUsername = "";
    ////--- The Database login Password 
    private string m_sPassword = "";
    ////--- The SQL Server instance 
    private string m_sServer = "";
    ////--- The Database name 
    private string m_sDatabase = "";
    ////--- The Database connection string 
    private string m_sConnectionString = "";
    ////Private m_bTraceFlag As Boolean = False '//--- The Database connection string 
    ////--- The array used to store the parameters to a stored procedure 
    private ArrayList m_oParmList = new ArrayList();

    ////---------------------------------------------------------------------------------------------------- 
    //// Class Constructor (zero arguments) 
    //// Overloaded: Yes 
    ////---------------------------------------------------------------------------------------------------- 
    public SqlService()
    {
        //m_sConnectionString = GetConnectionString("DBConnection");
        //m_sConnectionString = System.Configuration.ConfigurationSettings.AppSettings["StudentConnectionString"];
        m_sConnectionString = ConfigurationManager.ConnectionStrings["OrganizedInConnectionString"].ConnectionString;
       

       
    }

    /*private string GetConnectionString(string str)
    {
        //variable to hold our return value
        string conn = string.Empty;
        //check if a value was provided
        if (!string.IsNullOrEmpty(str))
        {
            //name provided so search for that connection
            conn = ConfigurationManager.ConnectionStrings[str].ConnectionString;
        }
        else
        //name not provided, get the 'default' connection
        {
            conn = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        }
        //return the value
        return conn;
    }
    */

    ////---------------------------------------------------------------------------------------------------- 
    //// Class Constructor (with the entire connection string as the argument) 
    //// Overloaded: Yes 
    ////---------------------------------------------------------------------------------------------------- 
    public SqlService(string sConnectionString)
    {
        m_sConnectionString = sConnectionString;
    }

    ////---------------------------------------------------------------------------------------------------- 
    //// Class Constructor (with connection string arguments) 
    //// Overloaded: Yes 
    ////---------------------------------------------------------------------------------------------------- 
    public SqlService(string sServer, string sDatabase, string sUsername, string sPassword)
    {
        Server = sServer;
        Database = sDatabase;
        Username = sUsername;
        Password = sPassword;
    }

    ////---------------------------------------------------------------------------------------------------- 
    //// Public R/W Property 
    //// Purpose: Exposes the connection string 
    ////---------------------------------------------------------------------------------------------------- 
    public string ConnectionString
    {
        get { return m_sConnectionString; }
        set { m_sConnectionString = value; }
    }

    ////---------------------------------------------------------------------------------------------------- 
    //// Public R/W Property 
    //// Purpose: Exposes the User ID 
    ////---------------------------------------------------------------------------------------------------- 
    public string Username
    {
        get { return m_sUsername; }
        set { m_sUsername = value; }
    }

    ////------------------------------------------------------------------------------------------------------------ 
    //// Public R/W Property 
    //// Purpose: Exposes the Password 
    ////------------------------------------------------------------------------------------------------------------ 
    public string Password
    {
        get { return m_sPassword; }
        set { m_sPassword = value; }
    }

    ////------------------------------------------------------------------------------------------------------------ 
    //// Public R/W Property 
    //// Purpose: Exposes the SQL Server 
    ////------------------------------------------------------------------------------------------------------------ 
    public string Server
    {
        get { return m_sServer; }
        set { m_sServer = value; }
    }

    ////------------------------------------------------------------------------------------------------------------ 
    //// Public R/W Property 
    //// Purpose: Exposes the Database name 
    ////------------------------------------------------------------------------------------------------------------ 
    public string Database
    {
        get { return m_sDatabase; }
        set { m_sDatabase = value; }
    }

    ////------------------------------------------------------------------------------------------------------------ 
    //// Public Method 
    //// Overloaded: Yes 
    //// Return Value: DataSet 
    //// Purpose: Executes a SQL statement. 
    ////------------------------------------------------------------------------------------------------------------ 
    public DataSet RunSql(string sSql, string sTableName)
    {

        SqlCommand oCmd = new SqlCommand();
        ////--- Create a new SqlCommand 
        SqlConnection oCn = null;
        ////--- Declare the SqlConnection 
        SqlDataAdapter oDa = new SqlDataAdapter();
        ////--- Create a new SqlDataAdapter 
        DataSet oDs = new DataSet();
        ////--- Create a new DataSet 

        ////--- Prepare connection to the database 
        oCn = Connect();
        {
            ////--- Set the CommandText, ActiveConnection and the Command Type 
            ////--- for the SqlCommand Object 
            oCmd.Connection = oCn;
            oCmd.CommandText = sSql;
            oCmd.CommandType = CommandType.Text;
        }
        {
            ////--- Assign the SqlCommand Object to the 
            ////--- Select command of the SqlDataAdapter and 
            oDa.SelectCommand = oCmd;

            ////--- Execute the Sql Statement and fill the dataset 
            oDa.Fill(oDs, sTableName);
        }

        ////--- Disconnect from the database 
        Disconnect(oCn);

        ////--- Return the DataSet 
        return oDs;
    }

    ////------------------------------------------------------------------------------------------------------------ 
    //// Public Method 
    //// Overloaded: Yes 
    //// Return Value: None 
    //// Purpose: Executes a SQL statement and returns nothing. 
    ////------------------------------------------------------------------------------------------------------------ 
    public Int32 RunSql(string sSql)
    {

        SqlCommand oCmd = new SqlCommand();
        ////--- Create a new SqlCommand 
        SqlConnection oCn = null;
        ////--- Declare the SqlConnection 
        Int32 oRowAffected = 0;
        ////---no of row affected by query execution 

        ////--- Prepare connection to the database 
        oCn = Connect();
        oCn.Open();
        {
            ////--- Execute the Sql Satement 
            oCmd.CommandText = sSql;
            oCmd.Connection = oCn;
            oCmd.CommandType = CommandType.Text;
            oRowAffected = oCmd.ExecuteNonQuery();
        }

        ////--- Disconnect from the database 
        Disconnect(oCn);
        return oRowAffected;
    }

    public Int32 RunSqlScalar(string sSql)
    {

        SqlCommand oCmd = new SqlCommand();
        ////--- Create a new SqlCommand 
        SqlConnection oCn = null;
        ////--- Declare the SqlConnection 
        Int32 oRowAffected = 0;
        ////---no of row affected by query execution 

        ////--- Prepare connection to the database 
        oCn = Connect();
        oCn.Open();
        {
            ////--- Execute the Sql Satement 
            oCmd.CommandText = sSql;
            oCmd.Connection = oCn;
            oCmd.CommandType = CommandType.Text;
            oRowAffected = Convert.ToInt32(oCmd.ExecuteScalar());
        }

        ////--- Disconnect from the database 
        Disconnect(oCn);
        return oRowAffected;
    }

    ////------------------------------------------------------------------------------------------------------------ 
    //// Public Method 
    //// Overloaded: Yes 
    //// Return Value: DataSet 
    //// Purpose: Executes a stored procedure. 
    ////------------------------------------------------------------------------------------------------------------ 
    public DataSet RunProc(string sProcName, string sTableName)
    {

        SqlCommand oCmd = new SqlCommand();
        ////--- Create a new SqlCommand 
        SqlConnection oCn = null;
        ////--- Declare the SqlConnection 
        SqlDataAdapter oDA = new SqlDataAdapter();
        ////--- Create a new SqlDataAdapter 
        DataSet oDs = new DataSet();
        ////--- Create a new DataSet 
        SqlParameter oSqlParameter = null;
        ////--- Declare a SqlParameter 
        Parameter oP = null;
        ////--- Declare a Parameter 
        ////--- Get an enumerator for the parameter array list 
        IEnumerator oEnumerator = m_oParmList.GetEnumerator();

        ////--- Prepare connection to the database 
        oCn = Connect();

        {
            ////--- Set the CommandText, ActiveConnection and the Command Type for the 
            ////--- SqlCommand Object 
            oCmd.Connection = oCn;
            oCmd.CommandText = sProcName;
            oCmd.CommandType = CommandType.StoredProcedure;
        }

        ////--- Loop through the Parameters in the ArrayList 
        while ((oEnumerator.MoveNext()))
        {
            oP = null;
            ////--- Get the current Parameter object 
            oP = (Parameter)(oEnumerator.Current);
            ////--- Instantiate a SqlParameter object 
            oSqlParameter = ConvertParameterToSqlParameter(oP);
            ////--- Add the SqlParameter object to the SqlCommand object 
            oCmd.Parameters.Add(oSqlParameter);
        }

        {
            ////--- Assign the SqlCommand Object to the 
            ////--- Select command of the SqlDataAdapter and 
            oDA.SelectCommand = oCmd;

            ////--- Execute the Stored Procedure and fill the dataset 
            oDA.Fill(oDs, sTableName);
        }

        ////--- Disconnect from the database 
        Disconnect(oCn);

        ////--- Return the DataSet 
        return oDs;

    }

    ////------------------------------------------------------------------------------------------------------------ 
    //// Public Method 
    //// Overloaded: Yes 
    //// Return Value: None 
    //// Purpose: Executes a stored procedure. 
    ////------------------------------------------------------------------------------------------------------------ 
    public void RunProc(string sProcName)
    {

        SqlCommand oCmd = new SqlCommand();
        ////--- Create a new SqlCommand 
        SqlConnection oCn = null;
        ////--- Declare the SqlConnection 
        SqlParameter oSqlParameter = null;
        ////--- Declare a SqlParameter 
        Parameter oP = null;
        ////--- Declare a Parameter 
        ////--- Get an enumerator for the parameter array list 
        IEnumerator oEnumerator = m_oParmList.GetEnumerator();

        ////--- Prepare connection to the database 
        oCn = Connect();

        oCn.Open();

        {
            ////--- Set the CommandText, ActiveConnection and the Command Type for the 
            ////--- SqlCommand Object 
            oCmd.Connection = oCn;
            oCmd.CommandText = sProcName;
            oCmd.CommandType = CommandType.StoredProcedure;
        }

        ////--- Loop through the Parameters in the ArrayList 
        while ((oEnumerator.MoveNext()))
        {
            oP = null;
            ////--- Get the current Parameter object 
            oP = (Parameter)(oEnumerator.Current);
            ////--- Instantiate a SqlParameter object 
            oSqlParameter = ConvertParameterToSqlParameter(oP);
            ////--- Add the SqlParameter object to the SqlCommand object 
            oCmd.Parameters.Add(oSqlParameter);
        }

        ////--- Execute the Stored Procedure 
        oCmd.ExecuteNonQuery();

        ////--- Disconnect from the database 
        Disconnect(oCn);
    }

    ////--------------------------------------------------------------------------------------- 
    //// Public Method 
    //// Overloaded: Yes 
    //// Return Value: None 
    //// Purpose: Adds a parameter for a stored procedure. 
    ////--------------------------------------------------------------------------------------- 
    public void AddParameter(string sParameterName, ssenumSqlDataTypes lSqlType, int iSize, string sValue)
    {


        SqlDbType eDataType = SqlDbType.VarChar;
        Parameter oParam = null;

        switch (lSqlType)
        {
            case ssenumSqlDataTypes.ssSDT_String:
                eDataType = SqlDbType.VarChar;
                break;
            case ssenumSqlDataTypes.ssSDT_Integer:
                eDataType = SqlDbType.Int;
                break;
            case ssenumSqlDataTypes.ssSDT_DateTime:
                eDataType = SqlDbType.DateTime;
                break;
            case ssenumSqlDataTypes.ssSDT_Bit:
                eDataType = SqlDbType.Bit;
                break;
            case ssenumSqlDataTypes.ssSDT_Decimal:
                eDataType = SqlDbType.Decimal;
                break;
            case ssenumSqlDataTypes.ssSDT_Money:
                eDataType = SqlDbType.Money;
                break;
        }

        oParam = new Parameter(sParameterName, eDataType, iSize, sValue);

        m_oParmList.Add(oParam);

    }

    ////--------------------------------------------------------------------------------------- 
    //// Private Method 
    //// Return Value: None 
    //// Purpose: Public Method that converts a Parameter to a SqlParameter 
    ////--------------------------------------------------------------------------------------- 
    private SqlParameter ConvertParameterToSqlParameter(Parameter oP)
    {
        ////--- Instantiate a SqlParameter object 
        SqlParameter oSqlParameter = new SqlParameter(oP.ParameterName, oP.DataType, oP.Size);

        {
            oSqlParameter.Value = oP.Value;
            oSqlParameter.Direction = oP.Direction;
        }

        return oSqlParameter;

    }

    ////--------------------------------------------------------------------------------------- 
    //// Private Method 
    //// Return Value: None 
    //// Purpose: Public Method that adds a parameter for a stored procedure. 
    ////--------------------------------------------------------------------------------------- 
    private SqlConnection Connect()
    {

        SqlConnection oCn = null;

        if ((m_sConnectionString.Length > 0))
        {
            oCn = new SqlConnection(m_sConnectionString);
        }
        else
        {
            oCn = new SqlConnection("Server=" + m_sServer + ";User ID=" + m_sUsername + ";Password=" + m_sPassword + ";Database=" + m_sDatabase + ";");
        }

        return oCn;

    }

    ////--------------------------------------------------------------------------------------- 
    //// Private Method 
    //// Return Value: None 
    //// Purpose: Close and destroy the Connection 
    ////--------------------------------------------------------------------------------------- 
    private void Disconnect(SqlConnection oCn)
    {
        ////--- Close the Connection if the Connection exists 
        if (((oCn != null) & (oCn.State != ConnectionState.Closed)))
        {
            oCn.Close();
        }

        ////--- Destroy the objects and release the memory 
        oCn = null;
    }

}

////--------------------------------------------------------------------------------------- 
//// Public Class 
//// Purpose: The parameter class defines a parameter that will be passed 
//// to a stored procedure 
////--------------------------------------------------------------------------------------- 
public class Parameter
{

    ////--- The datatype of the parameter 
    public SqlDbType DataType;
    ////--- The direction of the parameter 
    public ParameterDirection Direction;
    ////--- The Name of the parameter 
    public string ParameterName;
    ////--- The size of the parameter 
    public int Size;
    ////--- The value of the parameter 
    public string Value;

    ////---------------------------------------------------------------------------------------------------- 
    //// Class Constructor (zero arguments) 
    //// Overloaded: No 
    ////---------------------------------------------------------------------------------------------------- 
    public Parameter(string sParameterName, SqlDbType lDataType, int iSize, string sValue, ParameterDirection paraDirection)
    {
        ParameterName = sParameterName;
        DataType = lDataType;
        Size = iSize;
        Value = sValue;
        //Direction = ParameterDirection.Input;
        Direction = paraDirection;
    }

    public Parameter(string sParameterName, SqlDbType lDataType, int iSize, string sValue)
    {
        ParameterName = sParameterName;
        DataType = lDataType;
        Size = iSize;
        Value = sValue;
        Direction = ParameterDirection.Input;
    }

}